﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECOSYSTEM_Dominio;

namespace ECOSYSTEM_DAL
{
    public class GuiaRemisionDAL
    {
        SqlConnection cnn;
        Int32 CodigoGuia;
        public Int32 guardarGuiaRemision(GuiaRemision laGuiaRemision){
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("RegistrarGuiaRemision",cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("NroGuiaRem",laGuiaRemision.NroGuiaRem);
                cmd.Parameters.AddWithValue("FecReg", laGuiaRemision.Fecha);
                cmd.Parameters.AddWithValue("NroComprobante", laGuiaRemision.NroComprobante);
                cmd.Parameters.AddWithValue("Estado", laGuiaRemision.Estado);
                cmd.Parameters.AddWithValue("PuntoPartida", laGuiaRemision.PuntoPartida);
                cmd.Parameters.AddWithValue("TransportistaRUC",laGuiaRemision.ElTransportista);
                cmd.Parameters.AddWithValue("ClienteRUC", laGuiaRemision.ElCliente);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    CodigoGuia = Convert.ToInt32(dr["CodGuiaID"]);
                }
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return CodigoGuia;
        }
        public Boolean ActualizarGuiaRemision(GuiaRemision laGuiaRemision, Int32 IDRemGuia, String NroGuiaComparar)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ActualizarGuiaRemision", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("GuiaRemID",IDRemGuia);
                cmd.Parameters.AddWithValue("NroGuiaRem", laGuiaRemision.NroGuiaRem);
                cmd.Parameters.AddWithValue("NroGuiaRemComparar", NroGuiaComparar);
                cmd.Parameters.AddWithValue("FecReg", laGuiaRemision.Fecha);
                cmd.Parameters.AddWithValue("NroComprobante", laGuiaRemision.NroComprobante);
                cmd.Parameters.AddWithValue("Estado", laGuiaRemision.Estado);
                cmd.Parameters.AddWithValue("PuntoPartida", laGuiaRemision.PuntoPartida);
                cmd.Parameters.AddWithValue("TransportistaRUC", laGuiaRemision.ElTransportista);
                cmd.Parameters.AddWithValue("ClienteRUC", laGuiaRemision.ElCliente);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public Boolean guardarDetalleGuiaRemision(GuiaRemision.DetalleGuiaRemision elDetalleGuiaRemision)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("RegistrarDetalleGuiaRemision", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("GuiaRemisionID", elDetalleGuiaRemision.LaGuiaID);
                cmd.Parameters.AddWithValue("ProductoID", elDetalleGuiaRemision.ElProductoID);
                cmd.Parameters.AddWithValue("Cantidad", elDetalleGuiaRemision.Cantidad);
                cmd.Parameters.AddWithValue("UnidadMedida", elDetalleGuiaRemision.UnidadMedida);
                cmd.Parameters.AddWithValue("PesoTotal", elDetalleGuiaRemision.PesoTotal);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public Boolean ActualizarDetalleGuia(GuiaRemision.DetalleGuiaRemision elDetalleGuiaRemision)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ActualizarDetalleGuiaRemision", cnn);
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.Parameters.AddWithValue("IDGuia", elDetalleGuiaRemision.LaGuiaID);
                cmd.Parameters.AddWithValue("IDProducto", elDetalleGuiaRemision.ElProductoID);
                cmd.Parameters.AddWithValue("CantidadLlega", elDetalleGuiaRemision.Cantidad);
                cmd.Parameters.AddWithValue("UnidadMedida", elDetalleGuiaRemision.UnidadMedida);
                cmd.Parameters.AddWithValue("PesoTotal", elDetalleGuiaRemision.PesoTotal);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public DataTable ObtenerListaGuiasEmitidas(String FecEmision)
        {
            DataTable lista = null;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter("ObtenerListaGuias", cnn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("Fecha", FecEmision);
                lista = new DataTable();
                da.Fill(lista);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return lista;
        }
        public List<GuiaRemision> ObtenerGuiaEmitidaPorNro(String NroGuia)
        {
            List<GuiaRemision> lista;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("[ObtenerGuiaPoNro]", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("NroGuia", NroGuia);
                lista = new List<GuiaRemision>();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    GuiaRemision laGuia = new GuiaRemision();
                    laGuia.GuiaRemisionID = Convert.ToInt32(dr["GuiaRemisionID"]);
                    laGuia.NroGuiaRem = dr["NroGuiaRem"].ToString();
                    laGuia.Estado = dr["Estado"].ToString();
                    laGuia.PuntoPartida = dr["PuntoPartida"].ToString();
                    laGuia.Fecha = dr["FecReg"].ToString();
                    laGuia.NroComprobante = dr["NroComprobantePago"].ToString();
                    laGuia.ElCliente = dr["RUC_DNI_Cliente"].ToString();
                    laGuia.ElTransportista = dr["TransportistaID"].ToString();
                    lista.Add(laGuia);
                }
                return lista;
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public List<GuiaRemision.DetalleGuiaRemision> ObtenerDetalleGuiaEmitida(String NroGuiaID)
        {
            List<GuiaRemision.DetalleGuiaRemision> lista;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ObtenerDetalleGuiaEmitida", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("NroGuia", NroGuiaID);
                lista = new List<GuiaRemision.DetalleGuiaRemision>();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    GuiaRemision.DetalleGuiaRemision elDetalle = new GuiaRemision.DetalleGuiaRemision();
                    elDetalle.ElProductoID = Convert.ToInt32(dr["Codigo"]);                    
                    elDetalle.NameProduc = dr["NameProduc"].ToString();
                    elDetalle.Cantidad = Convert.ToInt32(dr["Cantidad"]);
                    elDetalle.UnidadMedida = dr["UnidadMedida"].ToString();
                    elDetalle.PesoTotal = Convert.ToDouble(dr["PesoTotal"]);
                    lista.Add(elDetalle);
                }
                return lista;
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
    }
}
